﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using NativeDataOperation.Business;
using NativeDataOperation.Interfaces;
using WebFoundation;
using WebFoundation.Data;

namespace DDHDeepTunnel.EquipmentManagement.Data
{
    public class SqlTableWritingDAO
    {
        public DbManager _dbManager;
        public IDbTransaction _transaction;

        public SqlTableWritingDAO(DbManager dbManager, IDbTransaction transaction = null)
        {
            _dbManager = dbManager;
            _transaction = transaction;
        }

        #region Insert batch •User-defined Findings : The summary of original method overwrites

        /// <summary>
        /// User-defined :insert batch dataTable
        /// </summary>
        /// <param name="dataTable">DataTable object</param>
        /// <param name="properties">List<SqlPropertyMap></param>
        /// <param name="tableName">string</param>
        /// <returns>int(insert datatable rows number)</returns>
        public string InsertBatchDataTable(DataTable dataTable, List<SqlPropertyMap> properties, string tableName)
        {
            var dataBaseBatch = new DataBaseBatch();
            return dataBaseBatch.InsertBatchDataTable(dataTable, properties, tableName, this._dbManager,
                this._transaction);
        }

        /// <summary>
        /// User-defined : use DataTable batch insert database.Furthermore: Insert containing GIS datas into table for PostGIS.
        /// </summary>
        /// <param name="dataTable">batch insert DataTable</param>
        /// <param name="properties">List<SqlPropertyMap></param>
        /// <param name="tableName">string</param>
        /// <param name="batchSize">batch insert number of datas</param>
        public string InsertBatchSqlDataTable(DataTable dataTable, List<SqlPropertyMap> properties,
            string tableName, int batchSize = 10000)
        {
            var dataBaseBatch = new DataBaseBatch();
            return dataBaseBatch.InsertBatchSqlDataTable(dataTable, properties, tableName, this._dbManager,
                this._transaction, batchSize);
        }

        /// <summary>
        /// User-defined :insert batch dictionary
        /// </summary>
        /// <param name="sqlString">string</param>
        /// <param name="destinationTableName">DestinationTableName</param>
        /// <param name="hashArrayParam">Dictionary<string, string></param>
        /// <param name="hashParamValue">Dictionary<string, object></param>
        /// <param name="arrayBindCount">int</param>
        /// <param name="transaction">IDbTransaction</param>
        /// <param name="connection">IDbConnection</param>
        /// <returns>bool</returns>
        public bool InsertBatchDictionary(string sqlString, string destinationTableName,
            Dictionary<string, string> hashArrayParam,
            Dictionary<string, object> hashParamValue, int arrayBindCount)
        {
            var dataBaseBatch = new DataBaseBatch();
            return dataBaseBatch.InsertBatchDictionary(sqlString, destinationTableName, hashArrayParam, hashParamValue, arrayBindCount, this._dbManager,
                this._transaction);
        }

        #endregion

        public string CreateTable(string tableName,
            List<SqlPropertyMap> properties = null, IDbConnection connection = null)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.CreateTable(tableName, this._dbManager, properties);
        }

        public string CreateTable(string tableName,
            List<SqlPropertyMap> properties = null)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.CreateTable(tableName, this._dbManager, properties);
        }

        public string DropTable(string tableName, IDbConnection connection = null)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.DropTable(tableName, this._dbManager);
        }

        public string DropTable(string tableName)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.DropTable(tableName, this._dbManager);
        }

        public string CloneTableStructure(string clonedTableName,
            string afterClonedTableName, IDbConnection connection = null)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.CloneTableStructure(clonedTableName, afterClonedTableName, connection);
        }

        public string CloneTableStructure(string clonedTableName,
            string afterClonedTableName)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.CloneTableStructure(clonedTableName, afterClonedTableName, this._dbManager);
        }

        public string CloneTableStructureAndData(string clonedTableName,
            string afterClonedTableName, IDbConnection connection = null)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.CloneTableStructureAndData(clonedTableName, afterClonedTableName, connection);
        }

        public string CloneTableStructureAndData(string clonedTableName,
            string afterClonedTableName)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.CloneTableStructureAndData(clonedTableName, afterClonedTableName, this._dbManager);
        }

        public DataTable GetDataTableRunSql(string sqlString, IDbConnection connection = null)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.GetDataTableRunSql(sqlString, connection);
        }

        public DataTable GetDataTableRunSql(string sqlString)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.GetDataTableRunSql(sqlString, this._dbManager);
        }

        public bool TableExist(string tableName, IDbConnection connection = null)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.TableExist(tableName, this._dbManager);
        }

        public bool TableExist(string tableName)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.TableExist(tableName, this._dbManager);
        }

        public string TruncateTableDatas(string tableName)
        {
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.TruncateTableDatas(tableName, this._dbManager);
        }

        public string DeleteTableDatas(string tableName, string whereParts)
        {
            //using (var transaction = this._dbManager.Connection.BeginTransaction(IsolationLevel.ReadCommitted))
            //{
            //    try
            //    {
            //        if (this._dbManager.Connection != null && this._dbManager.Connection.State.Equals(ConnectionState.Closed))
            //        {
            //            this._dbManager.Connection.Open();
            //        }
            //    }
            //    catch(Exception ex)
            //    {
            //        this.GetType().LogError(ex);
            //        this.GetType().LogInfo(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
            //        this._dbManager = new DbManager(this._dbManager.ConnectionStr, this._dbManager.DBType);
            //    }
            //}
            var dataBaseTable = new DataBaseTable();
            return dataBaseTable.DeleteTableDatas(tableName, whereParts, this._dbManager);
        }

        public int GetExcuteRunSql(string sqlString, IDbConnection connection = null)
        {
            var resultExcute = 0;
            using (connection)
            {
                var excuteOutResult = connection.Query(sqlString);
                IEnumerable<dynamic> outResult = excuteOutResult as dynamic[] ?? excuteOutResult.ToArray();
                if (outResult.Any())
                {
                    resultExcute = outResult.Count();
                }
            }
            return resultExcute;
        }

        public int GetExcuteRunSql(string sqlString)
        {
            var resultExcute = 0;
            using (this._dbManager.Connection)
            {
                var excuteOutResult = this._dbManager.Connection.Query(sqlString);
                IEnumerable<dynamic> outResult = excuteOutResult as dynamic[] ?? excuteOutResult.ToArray();
                if (outResult.Any())
                {
                    resultExcute = outResult.Count();
                }
            }
            return resultExcute;
        }
    }
}
